﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using OfficeOpenXml.Drawing.Chart;
using System.Windows.Forms.DataVisualization.Charting;
using System.Data.SqlClient;
using supermarket.method;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace supermarket
{
    public partial class frm_shopData : Form
    {
        public SqlConnection con = new SqlConnection();
        public frm_shopData ()
        {
            InitializeComponent();
            InitializeControls();
            UpdateChart();
        }

        private void InitializeControls ()
        {
            // 设置 ComboBox 默认值
            if (cmbChartType.Items.Count == 0)
            {
                cmbChartType.Items.AddRange(new object[] { "堆叠柱状图","柱状图"  });
                cmbChartType.SelectedIndex = 0;
            }         

            // 绑定事件
            cmbChartType.SelectedIndexChanged += ( s, e ) => UpdateChart();
            btnRefresh.Click += ( s, e ) => UpdateChart();

            // 初始化 Chart 控件
            chartSales.ChartAreas.Clear();
            chartSales.ChartAreas.Add(new ChartArea("MainArea"));
            chartSales.ChartAreas[0].AxisX.Title = "月份";
            chartSales.ChartAreas[0].AxisY.Title = "金额 (元)";
            chartSales.ChartAreas[0].AxisX.LabelStyle.Angle = 0;
            chartSales.ChartAreas[0].AxisX.IsLabelAutoFit = true;
            chartSales.ChartAreas[0].AxisX.MajorGrid.Enabled = false;
            chartSales.ChartAreas[0].AxisY.MajorGrid.Enabled = false;
        }

        private void UpdateChart ()
        {
            chartSales.Series.Clear();
            chartSales.Titles.Clear();
            chartSales.Titles.Add("门店销售额分析");

            try
            {
                Classgetsql sqlcon = new Classgetsql();
                con = sqlcon.getcon();

                string query = @"
                    SELECT shop_code, shop_name, month_date, sales
                    FROM super.dbo.shop_sales
                    ORDER BY shop_code, month_date";
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, con))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    string chartType = cmbChartType.SelectedItem?.ToString() ?? "堆叠柱状图";
                    string dataType =  "销售额";

                    SeriesChartType seriesChartType = chartType switch
                    {
                        "柱状图" => SeriesChartType.Column,
                        "堆叠柱状图" => SeriesChartType.StackedColumn,
                        _ => SeriesChartType.Line
                    };

                    string column =  "sales" ;
                    string legend = "销售额";

                    foreach (string shopName in dt.AsEnumerable().Select(row => row.Field<string>("shop_name")).Distinct())
                    {
                        Series series = chartSales.Series.Add(shopName);
                        series.ChartType = seriesChartType;
                        series.LegendText = $"{shopName} {legend}";
                        series.XValueType = ChartValueType.DateTime;
                        series.IsValueShownAsLabel = true;
                        series.LabelFormat = "N2";

                        var shopData = dt.AsEnumerable().Where(row => row.Field<string>("shop_name") == shopName);
                        foreach (var row in shopData)
                        {
                            DateTime month = row.Field<DateTime>("month_date");
                            series.Points.AddXY(month, row.Field<decimal>(column));
                        }
                    }
                    chartSales.ChartAreas[0].AxisX.LabelStyle.Format = "MMM";

                    chartSales.ChartAreas[0].AxisX.Title = "月份";
                    chartSales.ChartAreas[0].AxisY.Title = "金额 (元)";

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"加载图表数据时发生错误: {ex.Message}", "错误",
                               MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnmuban_Click ( object sender, EventArgs e )
        {
            try
            {
                DialogResult result = MessageBox.Show("是否下载门店数据分析模板？", "提示",
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (result == DialogResult.Yes)
                {
                    string templatePath = Path.Combine(Application.StartupPath, "DataTemplate", "门店数据分析模板.xlsx");

                    if (!File.Exists(templatePath))
                    {
                        MessageBox.Show("模板文件不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    SaveFileDialog saveFileDialog = new SaveFileDialog();
                    saveFileDialog.Filter = "Excel Files|*.xlsx";
                    saveFileDialog.Title = "保存模板文件";
                    saveFileDialog.FileName = "门店数据分析模板.xlsx";

                    if (saveFileDialog.ShowDialog() == DialogResult.OK)
                    {
                        File.Copy(templatePath, saveFileDialog.FileName, true);
                        MessageBox.Show("模板下载成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"下载模板时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnExport_Click ( object sender, EventArgs e )
        {
            try
            {
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter = "Excel Files|*.xlsx;*.xls";
                openFileDialog.Title = "选择要导入的Excel文件";

                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string filePath = openFileDialog.FileName;
                    DataTable dt = ExcelHelperNew.ImportFromExcel(filePath);

                    if (dt == null || dt.Rows.Count == 0)
                    {
                        MessageBox.Show("Excel文件中没有数据！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    // 开始导入数据
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }

                    using (SqlTransaction transaction = con.BeginTransaction())
                    {
                        try
                        {
                            string mergeQuery = @"
                                MERGE INTO shop_sales AS target
                                USING (VALUES (@shop_code, @shop_name, @month_date, @sales)) 
                                AS source (shop_code, shop_name, month_date, sales)
                                ON target.shop_code = source.shop_code 
                                AND target.month_date = source.month_date
                                WHEN MATCHED THEN
                                    UPDATE SET 
                                        target.shop_name = source.shop_name,
                                        target.sales = source.sales
                                WHEN NOT MATCHED THEN
                                    INSERT (shop_code, shop_name, month_date, sales)
                                    VALUES (source.shop_code, source.shop_name, source.month_date, source.sales);";

                            using (SqlCommand cmd = new SqlCommand(mergeQuery, con, transaction))
                            {
                                foreach (DataRow row in dt.Rows)
                                {
                                    cmd.Parameters.Clear();
                                    cmd.Parameters.AddWithValue("@shop_code", row["门店编码"] ?? DBNull.Value);
                                    cmd.Parameters.AddWithValue("@shop_name", row["门店名称"] ?? DBNull.Value);
                                    cmd.Parameters.AddWithValue("@month_date", Convert.ToDateTime(row["所属年月"]));
                                    cmd.Parameters.AddWithValue("@sales", Convert.ToDecimal(row["销售额"]));

                                    cmd.ExecuteNonQuery();
                                }
                            }

                            transaction.Commit();
                            MessageBox.Show($"成功导入/更新 {dt.Rows.Count} 条销售记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                            // 刷新图表显示
                            UpdateChart();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw new Exception($"导入数据时发生错误：{ex.Message}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导入数据时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

    }
}
